package repository;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import utils.SQLFactory;
import classes.Gender;
import classes.Iterator;
import classes.Member;
import exceptions.RepositoryException;

public class MemberRepository
{
	private static MemberRepository	instance;
	private Connection				connection;
	private PreparedStatement		addStatement;
	private PreparedStatement		containsStatement;
	private PreparedStatement		removeStatement;
	private PreparedStatement		editStatement;
	private PreparedStatement		getStatement;
	private PreparedStatement		iteratorStatement;
	private PreparedStatement		sizeStatement;

	private MemberRepository() throws RepositoryException
	{
		try
		{
			this.connection = SQLFactory.getConnectionInstance();
			this.init();
		}
		catch ( SQLException e )
		{
			throw new RepositoryException();
		}
	}

	public static MemberRepository getInstance() throws RepositoryException
	{
		if ( instance == null )
		{
			instance = new MemberRepository();
		}

		return instance;
	}

	private void init() throws RepositoryException
	{
		try
		{
			if ( connection != null && !connection.isClosed() )
			{
				this.addStatement = connection.prepareStatement( "insert into membro (nome, sexo, nascimento, telefone, eMail, comungante, endereco_id, sala_id) values(?,?,?,?,?,?,?,?)" );
				this.containsStatement = connection.prepareStatement( "select count(*) from membro where id = ?" );
				this.removeStatement = connection.prepareStatement( "delete from membro where id = ?" );
				this.editStatement = connection.prepareStatement( "update membro set nome = ?, sexo = ?, nascimento = ?, telefone = ?, eMail = ?, comungante = ?, endereco_id = ?, sala_id = ? where id = ?" );
				this.getStatement = connection.prepareStatement( "select * from membro where id = ?" );
				this.iteratorStatement = connection.prepareStatement( "select * from membro" );
				this.sizeStatement = connection.prepareStatement( "select count(*) from membro" );

				connection.setAutoCommit( false );
			}
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}
	}

	public void add( Member member ) throws RepositoryException
	{
		try
		{
			addStatement.clearParameters();
			addStatement.setString( 1, member.getName() );
			addStatement.setInt( 2, member.getGender().ordinal() );
			addStatement.setDate( 3, member.getBirth() );
			addStatement.setString( 4, member.getPhone() );
			addStatement.setString( 5, member.getEMail() );
			addStatement.setBoolean( 6, member.isComungant() );
			addStatement.setInt( 7, member.getAddressId() );
			addStatement.setInt( 8, member.getClassroomId() );
			addStatement.execute();
			connection.commit();
		}
		catch ( SQLException e )
		{
			try
			{
				connection.rollback();
			}
			catch ( SQLException e1 )
			{
			}

			throw new RepositoryException();
		}
	}

	public void close() throws RepositoryException
	{
		try
		{
			if ( !connection.isClosed() )
			{
				this.addStatement.close();
				this.editStatement.close();
				this.getStatement.close();
				this.iteratorStatement.close();
				this.removeStatement.close();
				this.sizeStatement.close();
				this.connection.close();
			}
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}
	}
	
	public boolean contains( int id ) throws RepositoryException
	{
		boolean ret;
		
		try
		{
			containsStatement.clearParameters();
			containsStatement.setInt( 1, id );
			ResultSet result = containsStatement.executeQuery();

			ret = false;
			if ( result.next() )
			{
				int count = result.getInt( 1 );
				ret = count == 1;
			}
			result.close();
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}
		
		return ret;
	}

	public void edit( Member member ) throws RepositoryException
	{
		try
		{
			editStatement.clearParameters();
			editStatement.setString( 1, member.getName() );
			editStatement.setInt( 2, member.getGender().ordinal() );
			editStatement.setDate( 3, member.getBirth() );
			editStatement.setString( 4, member.getPhone() );
			editStatement.setString( 5, member.getEMail() );
			editStatement.setBoolean( 6, member.isComungant() );
			editStatement.setInt( 7, member.getAddressId() );
			editStatement.setInt( 8, member.getClassroomId() );
			editStatement.setInt( 9, member.getId() );
			editStatement.execute();
			connection.commit();
		}
		catch ( SQLException e )
		{
			try
			{
				connection.rollback();
			}
			catch ( SQLException e1 )
			{
			}

			throw new RepositoryException();
		}
	}
	
	public Iterator< Member > find( Member member ) throws RepositoryException
	{
		Iterator< Member > ret;
		
		/*try
		{
			String query = String.format( "select * from sala where nome like \"%%%s%%\" and telefone like \"%%%s%%\" and eMail like \"%%%s%%\"", member.getName(), member.getPhone(), member.getEMail() );
			if( member.getGender() != null ) query = query + " and sexo = " + member.getGender().ordinal();
			if( member.getBirth() != null ) query = query + " and nascimento = " + member.getBirth();
			if( member.isComungant() != null ) query = query + " and nascimento = " + member.getBirth();
			Statement statement;
		
			statement = connection.createStatement();
		
			Vector< Member > vector = new Vector< Member >();
			ResultSet result = statement.executeQuery( query );
			while ( result.next() )
			{
				int id = result.getInt( "id" );
				String name = result.getString( "nome" );
				String magazine = result.getString( "revista" );
				int titularTeacherId = result.getInt( "titular_id" );
				int auxiliarTeacherId = result.getInt( "auxiliar_id" );
				Member member2 = new Member( name, magazine, titularTeacherId, auxiliarTeacherId );
				member2.setId( id );
				vector.add( member2 );
			}
			result.close();
	
			ret = new Iterator< Member >( vector );
		
		}
		catch ( SQLException e )
		{
			throw new RepositoryException();
		}*/
		ret = null;
		
		return ret;
	}

	public Member get( int id ) throws RepositoryException
	{
		Member ret;

		try
		{
			getStatement.clearParameters();
			getStatement.setInt( 1, id );
			ResultSet result = getStatement.executeQuery();

			ret = null;
			if ( result.next() )
			{
				String name = result.getString( "nome" );
				Gender gender = Gender.fromOrdinal( result.getInt( "sexo" ) );
				Date birth = result.getDate( "nascimento" );
				String phone = result.getString( "telefone" );
				String eMail = result.getString( "eMail" );
				boolean comungant = result.getBoolean( "comungante" );
				int addressId = result.getInt( "endereco_id" );
				int classroomId = result.getInt( "sala_id" );

				ret = new Member( name, gender, new utils.Date( birth.getTime() ), addressId, phone, eMail, comungant, classroomId );
				ret.setId( id );
			}
			result.close();
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}

		return ret;
	}

	public Iterator< Member > getIterator() throws RepositoryException
	{
		Iterator< Member > ret;

		try
		{
			Vector< Member > vetor = new Vector< Member >();
			ResultSet result = iteratorStatement.executeQuery();
			while ( result.next() )
			{
				int id = result.getInt( "id" );
				String name = result.getString( "nome" );
				Gender gender = Gender.fromOrdinal( result.getInt( "sexo" ) );
				Date birth = result.getDate( "nascimento" );
				String phone = result.getString( "telefone" );
				String eMail = result.getString( "eMail" );
				boolean comungant = result.getBoolean( "comungante" );
				int addressId = result.getInt( "endereco_id" );
				int classroomId = result.getInt( "sala_id" );

				Member member = new Member( name, gender, new utils.Date( birth.getTime() ), addressId, phone, eMail, comungant, classroomId );
				member.setId( id );
				vetor.add( member );
			}
			result.close();

			ret = new Iterator< Member >( vetor );
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}

		return ret;
	}

	public void remove( int id ) throws RepositoryException
	{
		try
		{
			removeStatement.clearParameters();
			removeStatement.setInt( 1, id );
			removeStatement.execute();
			connection.commit();
		}
		catch ( SQLException e )
		{
			try
			{
				connection.rollback();
			}
			catch ( SQLException e1 )
			{
			}

			throw new RepositoryException();
		}
	}

	public int size() throws RepositoryException
	{
		int ret;

		try
		{
			ResultSet result = sizeStatement.executeQuery();
			result.next();
			ret = result.getInt( 1 );
			result.close();
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}

		return ret;
	}
}
